﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.ComponentModel;

namespace EWDT_CDS_Hub.App_Code
{
    [DataObject]
    public class Subject
    {
        [DataObjectField(true,true)]
        public String Subject_id { get; set; }
        public String Subject_name { get; set; }
        public String Subject_desc { get; set; }
        public String Subject_type { get; set; }

        public static List<Subject> GetSubject()
        {
            List<Subject> results = new List<Subject>();
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EWDTDBString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Subject";
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    Subject s = new Subject();
                    s.Subject_id = (String)dr["subject_id"];
                    s.Subject_name = (String)dr["subject_name"];
                    s.Subject_desc = (String)dr["subject_desc"];
                    s.Subject_type = (String)dr["subject_type"];

                    results.Add(s);
                }
            }
            catch (SqlException e)
            {
            }
            finally
            {
                conn.Close();
            }

            return results;
        }

        public static int InsertSubject(Subject s)
        {
            int rowsInserted = 0;
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EWDTDBString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Subject (subject_id, subject_name, subject_desc, subject_type) VALUES (@id, @name, @desc, @type)";
                comm.Parameters.AddWithValue("@id", s.Subject_id);
                comm.Parameters.AddWithValue("@name", s.Subject_name);
                comm.Parameters.AddWithValue("@desc", s.Subject_desc);
                comm.Parameters.AddWithValue("@type", s.Subject_type);
                rowsInserted = comm.ExecuteNonQuery();

            }
            catch (SqlException ex)
            {

            }
            finally
            {
                conn.Close();
            }

            return rowsInserted;
        }

        public static int UpdateSubject(Subject s)
        {
            int rowsUpdated = 0;
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EWDTDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Subject SET subject_name = @name, subject_desc = @desc, subject_type = @type WHERE subject_id = @id";
                comm.Parameters.AddWithValue("@id", s.Subject_id);
                comm.Parameters.AddWithValue("@name", s.Subject_name);
                comm.Parameters.AddWithValue("@desc", s.Subject_desc);
                comm.Parameters.AddWithValue("@type", s.Subject_type);
                rowsUpdated = comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return rowsUpdated;
        }

        public static int VacancyOfSubject(Subject s)
        {
            int totalVacancy = 0;
            List<Subject> avail_subjectList = new List<Subject>();
            List<Class> classList = new List<Class>();
            avail_subjectList = StudentToClass.RetrieveAvailableSubjects();

            foreach (Subject subject in avail_subjectList)
            {
                if (s.Subject_id == subject.Subject_id)
                {
                    classList = Class.RetrieveClass(s.Subject_id);

                    foreach (Class c in classList)
                    {
                        totalVacancy += c.Class_vacancy;
                    }
                }
            }

            return totalVacancy;
        }

        public static Subject RetrieveSubject(String id)
        {
            Subject s = null;
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EWDTDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Subject WHERE subject_id = @id";
                comm.Parameters.AddWithValue("@id", id);
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    s = new Subject();
                    s.Subject_id = (String)dr["subject_id"];
                    s.Subject_name = (String)dr["subject_name"];
                    s.Subject_desc = (String)dr["subject_desc"];
                    s.Subject_type = (String)dr["subject_type"];
                }

                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return s;
        }

        //Get subjects that can be taken by the student
        //Use GetStudentDiploma(Student s) for id-----------vvvvvvvvvvv
        public static List<Subject> GetAvailableSubject(String id)
        {
            List<Subject> results = new List<Subject>();
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EWDTDBString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Subject WHERE subject_id NOT IN "+
                                   "(SELECT subject_id FROM SubjectToDiploma WHERE diploma_id = @id ";
                comm.Parameters.AddWithValue("@id", id);
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    Subject s = new Subject();
                    s.Subject_id = (String)dr["subject_id"];
                    s.Subject_name = (String)dr["subject_name"];
                    s.Subject_desc = (String)dr["subject_desc"];
                    s.Subject_type = (String)dr["subject_type"];

                    results.Add(s);
                }
            }
            catch (SqlException e)
            {
            }
            finally
            {
                conn.Close();
            }

            return results;
        }

        //[DataObjectMethod(DataObjectMethodType.Select)]
        public static List<Subject> RetrieveCurrentSubject(String id)
        {
            List<Subject> results = new List<Subject>();
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EWDTDBString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Subject WHERE subject_id IN" +
                                   "(SELECT subject_id FROM SubjectToStudent WHERE student_id = @id AND completed = @com)";
                comm.Parameters.AddWithValue("@id", id);
                comm.Parameters.AddWithValue("@com", 0);
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    Subject s = new Subject();
                    s.Subject_id = (String)dr["subject_id"];
                    s.Subject_name = (String)dr["subject_name"];
                    s.Subject_desc = (String)dr["subject_desc"];
                    s.Subject_type = (String)dr["subject_type"];

                    results.Add(s);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

            return results;
        }

        public static List<Subject> RetrievePastSubject(String id)
        {
            List<Subject> results = new List<Subject>();
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EWDTDBString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Subject WHERE subject_id IN" +
                                   "(SELECT subject_id FROM SubjectToStudent WHERE student_id = @id AND completed = @com)";
                comm.Parameters.AddWithValue("@id", id);
                comm.Parameters.AddWithValue("@com", 1);
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    Subject s = new Subject();
                    s.Subject_id = (String)dr["subject_id"];
                    s.Subject_name = (String)dr["subject_name"];
                    s.Subject_desc = (String)dr["subject_desc"];
                    s.Subject_type = (String)dr["subject_type"];

                    results.Add(s);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

            return results;
        }

        public static List<Subject> RetrieveChooseableSubject(String id)
        {
            List<Subject> results = new List<Subject>();
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EWDTDBString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Subject WHERE subject_id NOT IN" +
                                   "(SELECT subject_id FROM SubjectToStudent WHERE student_id = @id)";
                comm.Parameters.AddWithValue("@id", id);
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    Subject s = new Subject();
                    s.Subject_id = (String)dr["subject_id"];
                    s.Subject_name = (String)dr["subject_name"];
                    s.Subject_desc = (String)dr["subject_desc"];
                    s.Subject_type = (String)dr["subject_type"];

                    results.Add(s);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

            return results;
        }
    }   
}